﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Runtime.InteropServices;
using System.Data.SqlClient;
using System.Reflection;

namespace YTSK.Common
{
    class cDataGrid
    {
        #region 获取列及合计列的SQL语句
        /// <summary>
        /// 获取列及合计列的SQL语句
        /// </summary>
        /// <param name="moid">模板号</param>
        /// <param name="strSum">合计列的语句</param>
        /// <returns></returns>
        public static string GetColumnsStr(string moid, out string strSum)
        {
            var sql = string.Format(@"SELECT      cModeID, TableName, cDBName, cChinaName, iAlign, iWidth, bReadOnly, bVisible, cFormat, iOrder
FROM zGridColSet
WHERE(cModeID = '{0}')
ORDER BY iOrder", moid);
            var cmd = new SqlCommand();
            var dtcolumns = cmd.GetData(sql);
            string columnsStr = "";
            string columnsSum = "";
            for (int i = 0; i < dtcolumns.Rows.Count; i++)
            {
                var dtr = dtcolumns.Rows[i];
                var table = dtr["TableName"].ObjToString();
                if (table.Length > 0) { columnsStr += table + "."; }
                columnsStr += dtr["cDBName"].ObjToString() + ",";
                switch (dtcolumns.Rows[i]["cFormat"].ToString())
                {
                    case "#,##0.00":
                        columnsSum += string.Format("sum({0}) as {0},", dtr["cDBName"].ObjToString());
                        break;
                    default:
                        columnsSum += string.Format("null as {0},", dtr["cDBName"].ObjToString());
                        break;
                }
            }
            strSum = columnsSum.Substring(0, columnsSum.Length - 1);
            columnsStr = columnsStr.Substring(0, columnsStr.Length - 1);
            return columnsStr;
        }
        #endregion

        #region dataGridView的数据导出Excel格式
        /// <summary>
        /// dataGridView的数据导出Excel格式
        /// </summary>
        /// <param name="dv"></param>
        //public static void exportExcel(DataGridView dv)
        //{
        //    #region 保存的文件
        //    SaveFileDialog sf = new SaveFileDialog();
        //    sf.Filter = "xls文件(*.xls)|*.xls";
        //    DialogResult dr = sf.ShowDialog();
        //    if (dr != DialogResult.OK) return;
        //    string filename = sf.FileName;
        //    #endregion

        //    #region 数据转换为html
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("<html><head><meta content=\"text / html; charset = gb2312\"></head><table border=1><tr>");
        //    for (int k = 0; k <dv.Columns.Count ; k++)
        //    {
        //        sb.Append(string.Format("<th>{0}</th>", dv.Columns[k].HeaderText));
        //    }
        //    sb.Append("</tr>");
        //    //sb.Append(Environment.NewLine);

        //    for (int i = 0; i < dv.Rows.Count; i++)
        //    {
        //        sb.Append("<tr>");
        //        for (int j = 0; j < dv.Columns.Count; j++)
        //        {
        //            object oval = dv.Rows[i].Cells[j].Value;
        //            if (oval == null || oval == DBNull.Value) oval = "";
        //            string sretrun;
        //            switch(dv.Columns[j].DefaultCellStyle.Format)
        //            {
        //                case "yyyy-MM-dd":
        //                    DateTime dt0;
        //                    if (DateTime.TryParse(oval.ToString(), out dt0))
        //                    { sretrun = dt0.ToString("yyyy-MM-dd"); }
        //                    else
        //                    { sretrun = oval.ToString(); }
        //                    break;
        //                //case "0":
        //                //    sretrun = "'" + oval.ToString();
        //                //    break;
        //                case "#,##0.00":
        //                    double dval;
        //                    if (double.TryParse(oval.ToString(), out dval))
        //                    {
        //                        sretrun = dval.ToString("#,##0.00");
        //                    }
        //                    else
        //                    { sretrun = oval.ToString(); }
        //                    break;
        //                default:
        //                    sretrun = oval.ToString();
        //                    break;
        //            }
        //            string cformat = dv.Columns[j].DefaultCellStyle.Format;
        //            string excelFormat = "";
        //            if (cformat.Length > 0) { excelFormat = string.Format("style='mso-number-format: \"{0}\"; '", cformat); }

        //            sb.Append(string.Format("<td {1}>{0}</td>", sretrun,excelFormat));
        //            //sb.Append(dt.Rows[i][j].ToString() + "\t");
        //        }
        //        sb.Append("</tr>");
        //        //sb.Append(Environment.NewLine);
        //    }
        //    System.IO.StreamWriter writer1 = new System.IO.StreamWriter(filename, false, Encoding.Default);
        //    writer1.Write(sb.ToString());
        //    writer1.Close();
        //    #endregion

        //    #region 使用Excel打开，然后另存一下
        //    var excel = new Microsoft.Office.Interop.Excel.Application();
        //    excel.DisplayAlerts = false;
        //    excel.Visible = false;
        //    var xbook = excel.Workbooks.Open(filename);
        //    var filename1 = filename + "x";
        //    xbook.SaveAs(filename1, 51);
        //    excel.Quit();
        //    int hwmd = excel.Hwnd;
        //    KillExcel(hwmd);
        //    #endregion

        //    #region 打开Excel文件
        //    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        //    System.IO.File.Delete(filename);
        //    dr = MessageBox.Show(string.Format("保存成功，文件保存在{0}。是否立即打开？", filename1), "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
        //    if (dr == DialogResult.Yes)
        //    {
        //        System.Diagnostics.Process process = new System.Diagnostics.Process();
        //        process.StartInfo.FileName = filename1;
        //        process.Start();
        //    }
        //    #endregion
        //}
        #endregion

        #region 强制退掉Excel进程
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// 强制退出进程
        /// </summary>
        /// <param name="hwnd"></param>
        private static void KillExcel(int hwnd)
        {

            IntPtr t = new IntPtr(hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }
        #endregion

        #region 初始化DataGridView
        /// <summary>
        /// 初始化DataGridView
        /// </summary>
        /// <param name="_moid">模板号</param>
        /// <param name="dtv">待初始化的DataGrid</param>
        public static void initGrid(string _moid, DataGridView dtv)
        {
            string cmdsql = string.Format("select * from Ufsystem..zGridColSet where cModeID='{0}' order by iOrder ", _moid);
            DataTable dt = new DataTable();
            dtv.DataSource = null;
            dtv.Rows.Clear();
            dtv.Columns.Clear();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataGridViewTextBoxColumn dgvtb = new DataGridViewTextBoxColumn();
                dgvtb.DataPropertyName = dt.Rows[i]["cDBName"].ToString();
                dgvtb.Name = dt.Rows[i]["cDBName"].ToString();
                dgvtb.HeaderText = dt.Rows[i]["cChinaName"].ToString();
                switch (dt.Rows[i]["iAlign"].ObjToInt())
                {
                    case 1:
                        dgvtb.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                        break;
                    case 2:
                        dgvtb.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        break;
                    case 3:
                        dgvtb.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                        break;
                }
                dgvtb.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgvtb.Width = dt.Rows[i]["iWidth"].ObjToInt();
                switch (dt.Rows[i]["bVisible"].ObjToInt())
                {
                    case 1:
                        dgvtb.Visible = true;
                        break;
                    case 0:
                        dgvtb.Visible = false;
                        break;
                }
                string sFormat = dt.Rows[i]["cFormat"].ObjToString();
                if (sFormat.Length > 0)
                {
                    dgvtb.DefaultCellStyle.Format = sFormat;
                }
                dgvtb.DisplayIndex = i;
                dgvtb.SortMode = DataGridViewColumnSortMode.Automatic;
                dtv.Columns.Add(dgvtb);
            }
        }
        #endregion


        public static void DoubleBuffered(DataGridView dgv, bool setting)
        {
            Type dgvType = dgv.GetType();
            PropertyInfo pi = dgvType.GetProperty("DoubleBuffered", BindingFlags.Instance | BindingFlags.NonPublic);
            pi.SetValue(dgv, setting, null);
        }

    }
}
